package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

import com.sg.hms.Dao.DepartmentDao;
import com.sg.hms.Model.Department;
import com.sg.hms.Model.Department2;
import com.sg.hms.Model.User;
import com.sg.hms.Util.JDBCUtil;

public class DepartmentDaoImpl implements DepartmentDao{

	@Override
	public String selectDeNameBydId(int dId) {
		// TODO Auto-generated method stub
		String dname=null;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select dname from department where dId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, dId);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				dname=rs.getString("dName");
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dname;
	}
	
	//查询所有的部门集合的长度
	@Override
	public int getAllDepartmentsSize(){
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(dId) as dIds from department ");
		Connection conn = JDBCUtil.getConnection();//创建数据库连接
		try {
			PreparedStatement  pst = conn.prepareStatement(sql.toString());
			ResultSet set = pst.executeQuery();//执行sql语句
			if (set.next()) {
				count = set.getInt("dIds");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	// 根据部门名字添加部门
	@Override
	public boolean insertDepartment(String dName) {
		// TODO Auto-generated method stub
		boolean flag = false;
		StringBuffer sql = new StringBuffer("insert into department (dName) values(?)");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, dName);
			int a = pst.executeUpdate();
			if (a>0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateDepartment(String dName,int dId) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update department set dName = ? where dId = ?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, dName);
			pst.setInt(2, dId);
			int a = pst.executeUpdate();
			if (a>0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean deleteDepartmentById(String[] dIds) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("delete from department where dId in (?");
		for (int i = 1; i < dIds.length; i++) {
			sql.append(",?");
		}
		sql.append(")");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			for (int i = 0; i < dIds.length; i++) {
				pst.setString(i+1, dIds[i]);
			}
			int a = pst.executeUpdate();
			if (a>0) {
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	@Override
	public List<Department> selectDepartmentsBypage(int pageStart, int pageSize) {
		// TODO Auto-generated method stub
		List<Department> departments = new ArrayList<Department>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select dId,dName from department limit ?,? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1, pageStart);
			pst.setInt(2, pageSize);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				Department department = new Department();
				department.setdId(set.getInt("dId"));
				department.setdName(set.getString("dName"));
				departments.add(department);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return departments;
	}


}
